package com.situ.library.dao.impl;

import com.situ.library.dao.IUserDao;
import com.situ.library.pojo.entity.User;
import com.situ.library.pojo.query.UserQuery;
import com.situ.library.util.JDBCUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements IUserDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<User> selectByPage(UserQuery userQuery) {
        String sql = "select * from user where 1=1";
        //构造一个集合存放所有的条件
        List<Object> queryList = new ArrayList<>();
        String queryName = userQuery.getName();
        if (queryName != null && !queryName.equals("")) {
            sql += " and name like ?";
            queryList.add("%" + queryName + "%");
        }
        sql += " limit ?,?";
        int offset = (userQuery.getPage() - 1) * userQuery.getLimit();
        queryList.add(offset);
        queryList.add(userQuery.getLimit());
        List<User> list = template.query(sql,
                new BeanPropertyRowMapper<>(User.class), queryList.toArray());
        return list;
    }

    @Override
    public int selectTotalCount(UserQuery userQuery) {
        String sql = "select count(*) from user where 1=1";
        //构造一个集合存放所有的条件
        List<Object> queryList = new ArrayList<>();
        String queryName = userQuery.getName();
        if (queryName != null && !queryName.equals("")) {
            sql += " and name like ?";
            queryList.add("%" + queryName + "%");
        }
//        Integer count = template.queryForObject(sql, Integer.class,queryList);
//        return count;
        return template.queryForObject(sql, Integer.class, queryList.toArray());
    }

    @Override
    public void deleteById(int id) {
        template.update("delete from user where id=?", id);
    }

    @Override
    public void add(User user) {
        template.update("insert into user(id,name,password,nick_name,photo,role,email,phone) values(?,?,?,?,?,?,?)",
                user.getName(), user.getPassword(), user.getNickName(), user.getPhoto(), user.getRole(),
                user.getEmail(), user.getPhone());
    }

    @Override
    public User selectById(int id) {
        return template.queryForObject("select * from user where id=?",
                new BeanPropertyRowMapper<>(User.class), id);
    }

    @Override
    public void update(User user) {
        template.update("update user set name=?, password=?,phone=? where id=?",
                user.getName(), user.getPassword(), user.getPhone(), user.getId());
    }

    @Override
    public List<User> selectAll() {
        return template.query("select * from user", new BeanPropertyRowMapper<>(User.class));
    }
    @Override
    public User login(String name, String password) {
        try {
            return template.queryForObject("select id,name,password,role,nick_name,status,email,phone,photo from user where name=? and password=?",
                    new BeanPropertyRowMapper<>(User.class), name, password);
        } catch (EmptyResultDataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public void updateStatus(int id, int status) {
        template.update("update  user set status=? where id=?",
                status, id);
    }
}